package com.roc.web.action.ysb;

import java.util.ArrayList;
import java.util.List;

import com.roc.page.BaseSearchCondition;
import com.roc.page.WhereHQLFieldBean;
import com.roc.util.Constance;

public class YsbTermianlTransStaticsSearchCondition extends BaseSearchCondition{
	
	private String startingDate;
	private String endDate;
	private String name;  //机构名称
	private String code;  //区域编码

	public String getStartingDate() {
		return startingDate;
	}

	public void setStartingDate(String startingDate) {
		this.startingDate = startingDate;
	}

	public String getEndDate() {
		return endDate;
	}

	public void setEndDate(String endDate) {
		this.endDate = endDate;
	}
	

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public String getCode() {
		return code;
	}

	public void setCode(String code) {
		this.code = code;
	}

	@Override
	protected List<WhereHQLFieldBean> getWhereHQLFieldBeanList() {
		List<WhereHQLFieldBean> filedBeanList = new ArrayList<WhereHQLFieldBean>();
		if(Constance.isNotEmpty(startingDate))
		{
			filedBeanList.add(new WhereHQLFieldBean("cc.transDt", startingDate.trim(),">=", false));
		}
		if(Constance.isNotEmpty(endDate))
		{
			filedBeanList.add(new WhereHQLFieldBean("cc.transDt", endDate.trim(),"<=", false));
		}
		return filedBeanList;
	}

	@Override
	protected String getRowCountHQL() {
		StringBuffer sb=new StringBuffer();
		sb.append(" select count(1)                                                                    \n");
		sb.append("   from (select d.companyid companyName,                                            \n");
		sb.append("                d.real_name realName,                                               \n");
		sb.append("                d.trans_dt transDt,                                                 \n");
		sb.append("                count(1) transNum,                                                  \n");
		sb.append("                sum(d.amt) transAmt                                                 \n");
		sb.append("           from (select a.trans_at / 100 amt,                                       \n");
		sb.append("                        a.trans_dt,                                                 \n");
		sb.append("                        b.real_name,                                                \n");
		sb.append("                        b.companyid                                                 \n");
		sb.append("                   from apstfr.tbl_tfr_his_trn_log a,                               \n");
		sb.append("                        apsmgm.tbl_term_inf i,                                      \n");
		sb.append("                        apsmgm.t_pos_agent c,                                       \n");
		sb.append("                        (select o.id,                                               \n");
		sb.append("                                o.real_name,                                        \n");
		sb.append("                                (select aa.name                                     \n");
		sb.append("                                   from apsmgm.tbl_area aa                          \n");
		sb.append("                                  where aa.id = o.companyid) companyid              \n");
		sb.append("                           from apsmgm.t_operators o                                \n");
		sb.append("                          where ((o.role_type = '02' and o.supri_id <> '40001') or  \n");
		sb.append("                                (o.role_type = '03' and                             \n");
		sb.append("                                o.supri_id in                                       \n");
		sb.append("                                (select s.id                                        \n");
		sb.append("                                     from apsmgm.t_operators s                      \n");
		sb.append("                                    where s.role_type in ('02', '03')               \n");
		sb.append("                                      and s.supri_id = '40001')))                   \n");
		sb.append("                            and o.real_name not like '%测试%'                       \n");
		if(Constance.isNotEmpty(code)&&!code.equals("999")){
			sb.append(" and o.companyid =").append(code);
		}else{
			sb.append("  and o.companyid   in      (select id from apsmgm.tbl_area)   \n");
		}
		sb.append("          ) b        where i.mcht_cd = a.mchnt_cd                                      \n");
		sb.append("                    and substr(a.mchnt_cd, 0, 4) = '9527'                           \n");
		sb.append("                    and b.id = c.agent_id                                           \n");
		sb.append("                    and c.serial_num = i.device_cd                                  \n");
		sb.append("                    and a.trans_st = '1000000000'                                   \n");
		sb.append("                    and a.ma_trans_cd in ('1171', '1011')) d                        \n");
		sb.append("          group by d.trans_dt, d.real_name, d.companyid) cc    where 1=1                         \n");

		return sb.toString();
	}

	@Override
	protected String getSearchHQL() {
		StringBuffer sb=new StringBuffer();
		sb.append("  select * from (  select d.companyid companyName,                                             \n");
		sb.append("         d.real_name realName,                                                \n");
		sb.append("         d.trans_dt transDt,                                                  \n");
		sb.append("         count(1) transNum,                                                   \n");
		sb.append("         sum(d.amt) transAmt                                                  \n");
		sb.append("    from (select a.trans_at / 100 amt, a.trans_dt, b.real_name, b.companyid   \n");
		sb.append("            from apstfr.tbl_tfr_his_trn_log a,                                \n");
		sb.append("                 apsmgm.tbl_term_inf i,                                       \n");
		sb.append("                 apsmgm.t_pos_agent c,                                        \n");
		sb.append("                 (select o.id,                                                \n");
		sb.append("                         o.real_name,                                         \n");
		sb.append("                         (select aa.name                                      \n");
		sb.append("                            from apsmgm.tbl_area aa                           \n");
		sb.append("                           where aa.id = o.companyid) companyid               \n");
		sb.append("                    from apsmgm.t_operators o                                 \n");
		sb.append("                   where ((o.role_type = '02' and o.supri_id <> '40001') or   \n");
		sb.append("                         (o.role_type = '03' and                              \n");
		sb.append("                         o.supri_id in                                        \n");
		sb.append("                         (select s.id                                         \n");
		sb.append("                              from apsmgm.t_operators s                       \n");
		sb.append("                             where s.role_type in ('02', '03')                \n");
		sb.append("                               and s.supri_id = '40001')))                    \n");
		sb.append("                     and o.real_name not like '%测试%'                        \n");
		if(Constance.isNotEmpty(code)&&!code.equals("999")){
			sb.append(" and o.companyid =").append(code);
		}else{
			sb.append("  and o.companyid   in      (select id from apsmgm.tbl_area)   \n");
		}

		sb.append("       )b    where i.mcht_cd = a.mchnt_cd                                       \n");
		sb.append("             and substr(a.mchnt_cd, 0, 4) = '9527'                            \n");
		sb.append("             and b.id = c.agent_id                                            \n");
		sb.append("             and c.serial_num = i.device_cd                                   \n");
		sb.append("             and a.trans_st = '1000000000'                                    \n");
		sb.append("             and a.ma_trans_cd in ('1171', '1011')) d                         \n");
		sb.append("   group by d.trans_dt, d.real_name, d.companyid   )cc       where 1=1                     \n");
		return sb.toString();
	}

	@Override
	protected String getOrderPartOfHQL() {
		// TODO Auto-generated method stub
		return "order by cc.transDt desc  ";
	}

}
